Entertainment Location Projections
Authored by: Barkha Javed, Jack Pham
Duration: 75 mins
Level: Intermediate
Pre-requisite Skills: Python
Scenario

As a City of Melbourne council worker, I want to visualise and provide statistics on upcoming activities and planned works in entertainment and leisure, so that I can understand impact for my local area.

I also want to know which entertainment locations are projected as growth areas.

What this Use Case will teach you

At the end of this use case you will understand what entertainment and leisure activities are happening in a local area

This means learning how to:

  • Load and examine data on seating capacity of cafes, restaurants and pubs
  • Load and examine data on cafe, bistro, restaurant seats
  • Load and examine data for city activities and planned works
  • Load and examine pedestrian traffic to see current volumes for entertainment locations
  • Visualise information from the datasets
  • Review growth projections about entertainment locations
A brief introduction to the datasets used

Census of Land Use and Employment (CLUE)¶

The City of Melbourne conducts a census of all local businesses every two years. The last published survey was in 2020, the next survey results are expected soon.

The CLUE datasets contain information on venues:

  • CLUE Blocks spatial layer
  • Bar, tavern, pub patron capacity
  • Cafe, restaurant, bistro seats

City Activities and Planned Works¶

  • Geospatial events data, includes types such as traffic management, sport and recreation, reserved parking, public and private events

Other datasets of interest¶

  • Hourly pedestrian counts from sensors located across the city
  • Public and school holidays dataset
Accessing and Loading data
In [150]:
#Libraries to be installed
##!pip -q is to give less output
!pip -q install sodapy
!pip -q install seaborn
!pip -q install pandas
!pip -q install matplotlib
!pip -q install numpy
!pip -q install nbconvert
!pip -q install keyboard
!pip -q install geopandas
!pip -q install requests
!pip -q install folium
!pip -q install statsmodels
In [6]:
#load libraries
import os
import io
import timeF
import keyboard
import warnings
warnings.filterwarnings('ignore')
from datetime import datetime
import requests

import numpy as np
import pandas as pd
from sodapy import Socrata

from urllib.request import urlopen
import json

import folium
from folium import Choropleth, Circle, Marker
from folium.plugins import HeatMap, MarkerCluster

from IPython.core.display import display, HTML
import geopandas as gpd

from pandas.io.json import json_normalize
import plotly.express as px
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
from matplotlib import style
style.use('ggplot')

#Socrata client connection
client = Socrata('data.melbourne.vic.gov.au', '9UAGAvkem9fqXDhBLElapjazL', None)

Load CLUE blocks¶

Load the CLUE blocks geographical boundary layer to to link the CLUE information.

In [7]:
# spatial layer used to map CLUE datasets to CLUE blocks

domain = "data.melbourne.vic.gov.au"
geoJSON_Id = 'aia8-ryiq' #CLUE Blocks
#Call the API
GeoJSONURL = 'https://'+domain+'/api/geospatial/'+geoJSON_Id+'?method=export&format=GeoJSON'
with urlopen(GeoJSONURL) as response:
    clueblocks = json.load(response)

#sample - what does the data look like
clueblocks["features"][0]
Out[7]:
{'type': 'Feature',
 'properties': {'block_id': '662', 'clue_area': 'East Melbourne'},
 'geometry': {'type': 'Polygon',
  'coordinates': [[[144.9899553574, -37.8176128042],
    [144.9899751639, -37.8175252287],
    [144.9900850867, -37.8168872256],
    [144.9880108573, -37.8166537105],
    [144.9877992698, -37.816654024],
    [144.9876430254, -37.8166542547],
    [144.9860775351, -37.8164760542],
    [144.9860542492, -37.8164731858],
    [144.9853710501, -37.8163992503],
    [144.985196446, -37.8163794828],
    [144.9826415715, -37.8160938525],
    [144.9825871855, -37.8163908529],
    [144.982560267, -37.8165378587],
    [144.9851378107, -37.8168244375],
    [144.9851485344, -37.8167632769],
    [144.9854357153, -37.8167956425],
    [144.9854364507, -37.8167914483],
    [144.9875823469, -37.8170310783],
    [144.9879410231, -37.8170711275],
    [144.9878969846, -37.8173467142],
    [144.9888254215, -37.8174352545],
    [144.9888147558, -37.8174981936],
    [144.9899553574, -37.8176128042]]]}}

Load Bar, tavern, pub patron capacity¶

In [107]:
#Load Bar, tavern, pub patron capacity dataset
df_btp_capacity = pd.DataFrame.from_dict(client.get_all('mffi-m9yn'))
print(df_btp_capacity.shape)

integer_columns = ['census_year', 'block_id', 'property_id', 'base_property_id', 'number_of_patrons']
str_columns = ['street_address', 'clue_small_area', 'trading_name']
float_columns = ['x_coordinate', 'y_coordinate']
df_btp_capacity[integer_columns] = df_btp_capacity[integer_columns].astype(int)
df_btp_capacity[float_columns] = df_btp_capacity[float_columns].astype(float)
df_btp_capacity[str_columns] = df_btp_capacity[str_columns].astype(str)

df_btp_capacity.dropna(subset=['x_coordinate'])
df_btp_capacity.dropna(subset=['y_coordinate'])
df_btp_capacity.dropna(subset=['street_address'])

df_btp_capacity = df_btp_capacity.drop('geocoded_column',axis=1)

print(df_btp_capacity.shape)
print('\n',df_btp_capacity.info())

df_btp_capacity_y1=df_btp_capacity.query("census_year == 2018")
df_btp_capacity_y2=df_btp_capacity.query("census_year == 2019")
df_btp_capacity_y3=df_btp_capacity.query("census_year == 2020")
(4402, 12)
(4402, 11)
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 4402 entries, 0 to 4401
Data columns (total 11 columns):
 #   Column             Non-Null Count  Dtype  
---  ------             --------------  -----  
 0   census_year        4402 non-null   int32  
 1   block_id           4402 non-null   int32  
 2   property_id        4402 non-null   int32  
 3   base_property_id   4402 non-null   int32  
 4   street_address     4402 non-null   object 
 5   clue_small_area    4402 non-null   object 
 6   trading_name       4402 non-null   object 
 7   business_address   4402 non-null   object 
 8   number_of_patrons  4402 non-null   int32  
 9   x_coordinate       4382 non-null   float64
 10  y_coordinate       4382 non-null   float64
dtypes: float64(2), int32(5), object(4)
memory usage: 292.4+ KB

 None
In [108]:
df_btp_capacity_y1.head(5)
Out[108]:
census_year block_id property_id base_property_id street_address clue_small_area trading_name business_address number_of_patrons x_coordinate y_coordinate
3352 2018 6 578321 573333 2 Swanston Street MELBOURNE 3000 Melbourne (CBD) Beer De Luxe 2 Swanston Street MELBOURNE 3000 1000 144.969942 -37.817778
3353 2018 6 578324 573333 2 Swanston Street MELBOURNE 3000 Melbourne (CBD) Transport Hotel Tenancy 29, Ground , 2 Swanston Street MELBOUR... 600 144.969942 -37.817778
3354 2018 6 578324 573333 2 Swanston Street MELBOURNE 3000 Melbourne (CBD) Transit Rooftop Bar Tenancy 29, Level 2, 2 Swanston Street MELBOUR... 200 144.969942 -37.817778
3355 2018 6 578327 573333 2 Swanston Street MELBOURNE 3000 Melbourne (CBD) Zinc at Federation Square Tenancy 18, 2 Swanston Street MELBOURNE 3000 1000 144.969942 -37.817778
3356 2018 11 103984 103984 566-580 Flinders Street MELBOURNE 3000 Melbourne (CBD) UBar 8 Flinders Street MELBOURNE 3000 128 144.955336 -37.820711

Load Cafe, restaurant, bistro seats¶

In [22]:
#Load Cafe, restaurant, bistro seats dataset
df_crb = pd.DataFrame.from_dict(client.get_all('xt2y-tnn9'))

integer_columns = ['census_year', 'block_id', 'property_id', 'base_property_id', 'number_of_seats'
                   ,'industry_anzsic4_code']
str_columns = ['street_address', 'clue_small_area', 'trading_name','industry_anzsic4_description','seating_type']
float_columns = ['x_coordinate', 'y_coordinate']
df_crb[integer_columns] = df_crb[integer_columns].astype(int)
df_crb[float_columns] = df_crb[float_columns].astype(float)
df_crb[str_columns] = df_crb[str_columns].astype(str)

#drop NaN values
df_crb.dropna(subset=['x_coordinate'])
df_crb.dropna(subset=['y_coordinate'])
df_crb.dropna(subset=['street_address'])

#drop columns
df_crb = df_crb.drop('geocoded_column',axis=1)

print(df_crb.shape)
df_crb.head(5)
(56987, 14)
Out[22]:
census_year block_id property_id base_property_id street_address clue_small_area business_address trading_name industry_anzsic4_code industry_anzsic4_description seating_type number_of_seats x_coordinate y_coordinate
0 2002 2 111467 103973 0 King Street MELBOURNE 3000 Melbourne (CBD) 469-479 King Street MELBOURNE 3000 Melbourne Aquarium 8921 Zoological and Botanical Gardens Operation Seats - Indoor 113 144.957426 -37.82223
1 2002 2 111467 103973 0 King Street MELBOURNE 3000 Melbourne (CBD) 469-479 King Street MELBOURNE 3000 Melbourne Aquarium 8921 Zoological and Botanical Gardens Operation Seats - Outdoor 38 144.957426 -37.82223
2 2002 4 103972 103972 363-397 Flinders Street MELBOURNE 3000 Melbourne (CBD) Vault 12, 387 Flinders Street MELBOURNE 3000 Subway Sauna 9539 Other Personal Services n.e.c. Seats - Indoor 15 144.960985 -37.82115
3 2002 4 103972 103972 363-397 Flinders Street MELBOURNE 3000 Melbourne (CBD) Vault 1, 363-367 Flinders Street MELBOURNE 3000 Underworld Health & Fitness 9111 Health and Fitness Centres and Gymnasia Operation Seats - Indoor 20 144.960985 -37.82115
4 2002 4 103972 103972 363-397 Flinders Street MELBOURNE 3000 Melbourne (CBD) Vault 1, 363-367 Flinders Street MELBOURNE 3000 Underworld Health & Fitness 9111 Health and Fitness Centres and Gymnasia Operation Seats - Outdoor 18 144.960985 -37.82115
In [259]:
df_crb.census_year.unique()
Out[259]:
array([2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012,
       2013, 2014, 2015, 2016, 2017, 2018, 2019, 2020, 2021])
In [109]:
#create data frames per year for some visuals
df_crb_y1=df_crb.query("census_year == 2018")
df_crb_y2=df_crb.query("census_year == 2019")
df_crb_y3=df_crb.query("census_year == 2020")

df_crb_y3.head(5)
Out[109]:
census_year block_id property_id base_property_id street_address clue_small_area business_address trading_name industry_anzsic4_code industry_anzsic4_description seating_type number_of_seats x_coordinate y_coordinate
50593 2020 1 611394 611394 545-557 Flinders Street MELBOURNE VIC 3000 Melbourne (CBD) 551 Flinders Street MELBOURNE VIC 3000 Nandos Northbank 4511 Cafes and Restaurants Seats - Indoor 60 144.956514 -37.820979
50594 2020 1 611394 611394 545-557 Flinders Street MELBOURNE VIC 3000 Melbourne (CBD) 551 Flinders Street MELBOURNE VIC 3000 Nandos Northbank 4511 Cafes and Restaurants Seats - Outdoor 6 144.956514 -37.820979
50595 2020 1 611394 611394 545-557 Flinders Street MELBOURNE VIC 3000 Melbourne (CBD) 547 Flinders Street MELBOURNE VIC 3000 Tokyo Maki 4511 Cafes and Restaurants Seats - Indoor 44 144.956514 -37.820979
50596 2020 1 611394 611394 545-557 Flinders Street MELBOURNE VIC 3000 Melbourne (CBD) 553 Flinders Street MELBOURNE VIC 3000 Domino's Pizza 4512 Takeaway Food Services Seats - Indoor 12 144.956514 -37.820979
50597 2020 1 611395 611395 561-581 Flinders Street MELBOURNE VIC 3000 Melbourne (CBD) 563 Flinders Street MELBOURNE VIC 3000 Di Parsia Pasticceria Cafe 4512 Takeaway Food Services Seats - Outdoor 16 144.955901 -37.821088

Load City Activities and Planned Works¶

Load spatial layer

In [461]:
# spatial layer used to map city activity planned works
domain = "data.melbourne.vic.gov.au"
geoJSON_Id = 'txcy-uafv' 

#Call the API
GeoJSONURL = 'https://'+domain+'/api/geospatial/'+geoJSON_Id+'?method=export&format=GeoJSON'
with urlopen(GeoJSONURL) as response:
    capw = json.load(response)

capw['features'][0]
Out[461]:
{'type': 'Feature',
 'properties': {'start_date': '2022-04-25T00:00:00.000',
  'location': '17-23 Wills StreetMELBOURNE VIC 3000',
  'activity_id': 'SS-1076969-0-110168-ECW-Consent Extended-250420220700-050620222359',
  'end_date': '2022-06-05T00:00:00.000',
  'status': 'Confirmed',
  'source_id': 'ECW-2021-334',
  'notes': 'ePathway Consent for works',
  'classification': 'Structures',
  'small_area': 'Melbourne (CBD)'},
 'geometry': {'type': 'MultiPolygon',
  'coordinates': [[[[144.956746568433, -37.810915822759],
     [144.95682070225, -37.811075418607],
     [144.957224684897, -37.810955012315],
     [144.957213197241, -37.810929720695],
     [144.957153556213, -37.810798412985],
     [144.956746568433, -37.810915822759]]]]}}
In [462]:
df_capw=json_normalize(capw['features'])
In [464]:
#rename columns
df_capw.rename(columns={"properties.start_date": "start_date", "properties.location": "location"
                        ,"properties.activity_id":"activity_id", "properties.end_date":"end_date"
                        ,"properties.status":"status", "properties.source_id":"source_id"
                        ,"properties.notes":"notes", "properties.classification":"classification"
                        ,"properties.small_area":"small_area"}
               ,inplace = True)

#Convert to date, add columns
df_capw['start_dt'] = pd.to_datetime(df_capw.start_date).dt.date
df_capw['start_year'] = pd.to_datetime(df_capw.start_dt).dt.year
df_capw['start_month'] = pd.to_datetime(df_capw.start_dt).dt.month

#drop columns
#df_capw = df_capw.drop(['geometry.type', 'type'], axis=1)

#filter found there are records with value 2921-11-19 00:00:00, exclude these
df_capw = df_capw.loc[(df_capw['end_date'] < '2065-01-01')]
df_capw['end_dt'] = pd.to_datetime(df_capw.end_date).dt.date
df_capw['end_year'] = pd.to_datetime(df_capw.end_dt).dt.year

df_capw.head(5).T
Out[464]:
0 1 2 3 4
type Feature Feature Feature Feature Feature
start_date 2022-04-25T00:00:00.000 2022-01-01T00:00:00.000 2022-05-15T00:00:00.000 2022-05-07T00:00:00.000 2022-03-31T00:00:00.000
location 17-23 Wills StreetMELBOURNE VIC 3000 Therry Street between Victoria Street and Eliz... Canning Street between Dryburgh Street and Shi... 111-149 Nicholson StreetCARLTON VIC 3053 24-30 Barkly PlaceCARLTON VIC 3053
activity_id SS-1076969-0-110168-ECW-Consent Extended-25042... WO-728357-1554230-1554230-61-Closed-0101202200... RP-59095-193964-21212-COU-Confirmed-1505202200... SS-1102147-0-107153-ECW-Consent Extended-07052... SS-1108795-0-100732-EHD-Permit Issued-31032022...
end_date 2022-06-05T00:00:00.000 2022-06-30T00:00:00.000 2022-05-15T00:00:00.000 2022-06-02T00:00:00.000 2022-07-30T00:00:00.000
status Confirmed CONFIRMED CONFIRMED Confirmed CONFIRMED
source_id ECW-2021-334 728357 33104 ECW-2022-24 EHD-2021-89/1
notes ePathway Consent for works - None ePathway Consent for works Hoarding
classification Structures Traffic Management Reserved Parking Structures Structures
small_area Melbourne (CBD) Melbourne (CBD) North Melbourne Carlton Carlton
geometry.type MultiPolygon MultiPolygon MultiPolygon MultiPolygon MultiPolygon
geometry.coordinates [[[[144.956746568433, -37.810915822759], [144.... [[[[144.961542602277, -37.80668632029], [144.9... [[[[144.943314574201, -37.796187976188], [144.... [[[[144.974143318394, -37.796803959485], [144.... [[[[144.961571195434, -37.802915317956], [144....
start_dt 2022-04-25 2022-01-01 2022-05-15 2022-05-07 2022-03-31
start_year 2022 2022 2022 2022 2022
start_month 4 1 5 5 3
end_dt 2022-06-05 2022-06-30 2022-05-15 2022-06-02 2022-07-30
end_year 2022 2022 2022 2022 2022
In [453]:
print(df_capw.shape)
(600, 15)
In [454]:
df_capw.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 600 entries, 0 to 604
Data columns (total 15 columns):
 #   Column                Non-Null Count  Dtype 
---  ------                --------------  ----- 
 0   start_date            600 non-null    object
 1   location              598 non-null    object
 2   activity_id           600 non-null    object
 3   end_date              600 non-null    object
 4   status                600 non-null    object
 5   source_id             600 non-null    object
 6   notes                 495 non-null    object
 7   classification        600 non-null    object
 8   small_area            600 non-null    object
 9   geometry.coordinates  600 non-null    object
 10  start_dt              600 non-null    object
 11  start_year            600 non-null    int64 
 12  start_month           600 non-null    int64 
 13  end_dt                600 non-null    object
 14  end_year              600 non-null    int64 
dtypes: int64(3), object(12)
memory usage: 75.0+ KB
In [455]:
#Range of years
df_capw.start_year.unique()
Out[455]:
array([2022, 2018, 2021, 2019, 2020, 2023], dtype=int64)

View Bar, tavern, pub patron capacity¶

In [306]:
# Display the choropleth map
fig = px.choropleth_mapbox(
        
    df_btp_capacity, #dataset
    geojson=clueblocks, #CLUE Block spatial data
        
    locations='block_id', 
    color='number_of_patrons', 
    color_continuous_scale='ylgn', #colour scale
    range_color=(0, df_btp_capacity['number_of_patrons'].max()), #range for the colour scale
        
    featureidkey="properties.block_id",
    mapbox_style="carto-positron", #map style
    zoom=13.25, #zoom level
    
    center = {"lat": -37.81216592937499, "lon": 144.961812290625}, # set the map centre coordinates on Melbourne
    opacity=0.7,
        
    hover_name='clue_small_area', #title of the pop up box
    hover_data={'block_id':True,'number_of_patrons':True}, #values to display in the popup box
        
    labels={'number_of_patrons':'Number of Patrons','block_id':'Block Id'},
    title='Bar, tavern, pub patron capacity', #Title for plot
    width=950, height=800 #dimensions of plot in pixels

 )

#show year 1
fig1 = px.scatter_mapbox(
    
    df_btp_capacity_y1, lat="y_coordinate", lon="x_coordinate",
    opacity=0.7,
    hover_name='clue_small_area', # the title of the hover pop up box
     hover_data={'census_year':True,'block_id':True,'number_of_patrons':True,
                 'y_coordinate':False,'x_coordinate':False}, #values to display in the popup box
    color_discrete_sequence=['cyan'],   
    labels={'number_of_patrons':'Number of Patrons','block_id':'Block Id',
            'census_year':'Census Year'}, # defines labels for
    
)

#show year 2
fig2 = px.scatter_mapbox(
    
    df_btp_capacity_y2, lat="y_coordinate", lon="x_coordinate",
    opacity=0.7,
    hover_name='clue_small_area', # the title of the hover pop up box
    hover_data={'census_year':True,'block_id':True,'number_of_patrons':True,
                 'y_coordinate':False,'x_coordinate':False}, #values to display in the popup box
    color_discrete_sequence=['plum'],   
    labels={'number_of_patrons':'Number of Patrons','block_id':'Block Id',
            'census_year':'Census Year'}, # defines labels for
    
)

#show year 3
fig3 = px.scatter_mapbox(
    
    df_btp_capacity_y3, lat="y_coordinate", lon="x_coordinate",
    opacity=0.75,
    hover_name='clue_small_area', #title of the pop up box
    hover_data={'census_year':True,'block_id':True,'number_of_patrons':True,
                 'y_coordinate':False,'x_coordinate':False}, #values to display in the popup box
    color_discrete_sequence=['purple'],   
    labels={'number_of_patrons':'Number of Patrons','block_id':'Block Id',
            'census_year':'Census Year'}, #labels
    
)

fig.add_trace(fig.data[0])

#differentiate recent years for interest
fig.add_trace(fig1.data[0])
fig.add_trace(fig2.data[0])
fig.add_trace(fig3.data[0])
fig.update_geos(fitbounds="locations", visible=True)

fig.show()

View Cafe, restaurant, bistro seats¶

In [272]:
# Display the choropleth map
fig = px.choropleth_mapbox(
        
    df_crb, #dataset
    geojson=clueblocks, #CLUE Block spatial data
        
    locations='block_id', 
    color='number_of_seats', 
    color_continuous_scale='ylgn', # colour scale
    range_color=(0, df_crb['number_of_seats'].max()), #range for the colour scale
        
    featureidkey="properties.block_id", #polygon identifier from the GeoJSON data
    mapbox_style="carto-positron", # map style
    zoom=12.25, # set the zoom level
    
    center = {"lat": -37.81216592937499, "lon": 144.961812290625}, # set the map centre coordinates on Melbourne
    opacity=0.7, 
        
    hover_name='clue_small_area', # the title of the hover pop up box
    hover_data={'block_id':True,'number_of_seats':True,'seating_type':True}, #values to display in the popup box
    color_discrete_sequence=['green'],
    
    labels={'number_of_seats':'Number of Seats','block_id':'Block Id',
            'seating_type':'Seating Type'}, # defines labels for
    title='Cafe, restaurant, bistro seats', # Title for plot
    width=950, height=800 # dimensions of plot in pixels

 )

fig1 = px.scatter_mapbox(
    
    df_crb_y1, lat="y_coordinate", lon="x_coordinate",
    opacity=0.7,
    hover_name='clue_small_area', # the title of the hover pop up box
     hover_data={'census_year':True,'block_id':True,'number_of_seats':True,
                'seating_type':True, 'y_coordinate':False, 
                'x_coordinate':False}, #values to display in the popup box
    color_discrete_sequence=['green'],   
    labels={'number_of_seats':'Number of Seats','block_id':'Block Id','seating_type':'Seating Type',
            'census_year':'Census Year'}, # defines labels for
    
)

fig2 = px.scatter_mapbox(
    
    df_crb_y2, lat="y_coordinate", lon="x_coordinate",
    opacity=0.7,
    hover_name='clue_small_area', # the title of the hover pop up box
    hover_data={'census_year':True,'block_id':True,'number_of_seats':True,
                'seating_type':True, 'y_coordinate':False, 
                'x_coordinate':False}, #values to display in the popup box
    color_discrete_sequence=['purple'],   
    labels={'number_of_seats':'Number of Seats','block_id':'Block Id','seating_type':'Seating Type',
            'census_year':'Census Year'}, # defines labels
    
)

fig2 = px.scatter_mapbox(
    
    df_crb_y3, lat="y_coordinate", lon="x_coordinate",
    opacity=0.7,
    hover_name='clue_small_area', # the title of the hover pop up box
    hover_data={'census_year':True,'block_id':True,'number_of_seats':True,
                'seating_type':True, 'y_coordinate':False, 
                'x_coordinate':False}, #values to display in the popup box
    color_discrete_sequence=['purple'],   
    labels={'number_of_seats':'Number of Seats','block_id':'Block Id','seating_type':'Seating Type',
            'census_year':'Census Year'}, # defines labels
    
)

fig.add_trace(fig.data[0])
fig.add_trace(fig1.data[0])
fig.add_trace(fig2.data[0])
fig.add_trace(fig3.data[0])
fig.update_geos(fitbounds="locations", visible=False)

fig.show()

View City Planned Activities¶

In [469]:
capw['features'][0]
Out[469]:
{'type': 'Feature',
 'properties': {'start_date': '2022-04-25T00:00:00.000',
  'location': '17-23 Wills StreetMELBOURNE VIC 3000',
  'activity_id': 'SS-1076969-0-110168-ECW-Consent Extended-250420220700-050620222359',
  'end_date': '2022-06-05T00:00:00.000',
  'status': 'Confirmed',
  'source_id': 'ECW-2021-334',
  'notes': 'ePathway Consent for works',
  'classification': 'Structures',
  'small_area': 'Melbourne (CBD)'},
 'geometry': {'type': 'MultiPolygon',
  'coordinates': [[[[144.956746568433, -37.810915822759],
     [144.95682070225, -37.811075418607],
     [144.957224684897, -37.810955012315],
     [144.957213197241, -37.810929720695],
     [144.957153556213, -37.810798412985],
     [144.956746568433, -37.810915822759]]]]}}
In [512]:
fig = px.choropleth_mapbox(
        
    df_capw, #dataset
    geojson=capw,     
    
    locations='activity_id', 
    color='end_year', 
    color_continuous_scale='viridis',
    
    featureidkey="properties.activity_id", #polygon identifier from the GeoJSON data
    mapbox_style="carto-positron", # map style
    zoom=13, # set the zoom level
    
    center = {"lat": -37.81216592937499, "lon": 144.961812290625}, # set the map centre coordinates on Melbourne
        
    hover_name='small_area', # the title of the hover pop up box
    hover_data={'activity_id':False, 'classification':True,
                'start_year':True,'end_year':True, 'source_id': True}, #values to display in the popup box
    
    #defines labels
    labels={'source_id':'Source_Id', 'classification':'Classification',
            'start_year':'Start Year',
            'end_year':'End Year'}, 
    title='Planned Activity and Works', 
    width=950, height=800

 )
fig.show()
Statistics

Charts

Jack: Top locations by seats outdoors and indoors Top locations by number of patrons Top entertainment locations with activities in 2022

Barkha: What is the pedestrian traffic for these locations, also day and night Are the venues busier in the evenings Are any venues busier in the day

Bonus: Can we make a selection to see breakdown for a location dropdown list?

Or focus on the red areas for venues example Docklands

In [ ]:
 
Projections

Add narrative

In [ ]:
#Model for forecasting location growth using datasets loaded earlier

#Todo:
#Initial linear regression, compare to another model LSTM

#Techniques for controlling jittering
#Normalise data to 28 day period per month example 28/31 * measure
# eg:  28/31 * pedestrian count


#look at areas with high demand based on pedestrian traffic by month, dow and hod
In [146]:
import matplotlib.pyplot as plt
from pandas.plotting import register_matplotlib_converters
register_matplotlib_converters()
plt.style.use("ggplot")

ds_sensor_avg_dow.plot(color="purple")
Out[146]:
<AxesSubplot:>
In [ ]:
from statsmodels.tsa.seasonal import seasonal_decompose
result = seasonal_decompose(data_df)
fig = result.plot()
Summary
In [ ]:
 
References

City of Melbourne Open Data Team, 2014 - 2021,'Bar, tavern, pub patron capacity 2020', City of Melbourne, date retrieved 26 Nov 2022, https://data.melbourne.vic.gov.au/Business/Bar-tavern-pub-patron-capacity-2020/9hjf-8i2d

City of Melbourne Open Data Team, 2014 - 2021,'Cafe, restaurant, bistro seats 2020', City of Melbourne, date retrieved 26 Nov 2022, https://data.melbourne.vic.gov.au/Business/Cafe-restaurant-bistro-seats-2020/dyqx-cfn5

City of Melbourne Open Data Team, 2014 - 2021,'City Activities and Planned Works', City of Melbourne, date retrieved 26 Nov 2022, https://data.melbourne.vic.gov.au/Events/City-Activities-and-Planned-Works/txcy-uafv

City of Melbourne Open Data Team, 2014 - 2021,'Pedestrian Counting System - Monthly (counts per hour)', City of Melbourne, date retrieved 03 Dec 2022, https://dev.socrata.com/foundry/data.melbourne.vic.gov.au/b2ak-trbp

City of Melbourne Open Data Team, 2014 - 2021,'Pedestrian Counting System - Sensor Locations', City of Melbourne, date retrieved 03 Dec 2022, https://data.melbourne.vic.gov.au/Transport/Pedestrian-Counting-System-Sensor-Locations/h57g-5234

O'Brien J, et al., 2020, 'Covid 19 in Australia', covid19data.com.au, date retrieved 03 Dec 2022, https://www.covid19data.com.au/

In [ ]:
#save notebook, required so that step to convert to html, writes latest results to file
#adapt for other OS, this is for Windows
keyboard.press_and_release('ctrl+s')

!jupyter nbconvert  usecase_entertainment_location_projections.ipynb --to html --log-level WARN
In [ ]: